"""
飞检数据STEP8筛选 - clue_bad_drug.py
============================================================================

1. 读取 clue868_library.xlsx 文件，获取超量线索库
2. 库数据比对
    2.1. 获取 scene_clue_data 表中 item_code 表与库对应的数据
    2.2. 获取 scene_clue_data 表中 item_name 表与库对应的数据
    2.3. name 与 code 比对，判断是否 name 的数据更多，如果是，则要求 input，用户可以选择扩充库还是继续运行
    2.4. 如果是扩充库，则输出一个临时表，按clue868_library的原格式，固定 item_code 与 item_name 与 unit，将多出来的部分列出
3. 计算超量

============================================================================
"""

import json
import pandas as pd
from jbar import bar
from sqlalchemy import create_engine, text
import re
import os
import sys
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
# 读取 .env 文件
import dotenv
from config import create_db_engine
# 创建数据库引擎
engine = create_db_engine()
dotenv.load_dotenv()
# 从 common.rule_flatten_handle 中 导入 flatten_json, unflatten_json
from common.rule_flatten_handle import flatten_json, unflatten_json
import time

sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
from config import create_db_engine

from sqlalchemy.engine import Engine


_ILLEGAL_CHAR_RE = re.compile(r"[\x00-\x08\x0b\x0c\x0e-\x1f]")

def clean_illegal_chars(s: str) -> str:
    """Remove illegal characters from a string."""
    if pd.isna(s):
        return s
    if isinstance(s, str):
        return _ILLEGAL_CHAR_RE.sub("", s)
    return s

# 时间函数
t0 = time.time()
def elapsed() -> str:
    timeStr = time.strftime("%H:%M:%S", time.localtime())
    delta = int(time.time() - t0)
    if delta < 60:
        return f"{timeStr} (+ {delta} sec)"
    elif delta < 3600:
        m, s = divmod(delta, 60)
        return f"{timeStr} (+ {m} min {s} sec)"
    elif delta < 86400:
        h, rem = divmod(delta, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {h} hour {m} min {s} sec)"
    else:
        d, rem = divmod(delta, 86400)
        h, rem = divmod(rem, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {d} day {h} hour {m} min {s} sec)"
    
# 创建数据库引擎
engine = create_db_engine()
def load_hsp_list() -> list:
    """Load hospital list from a JSON file."""
    with open('hspList.json', 'r', encoding='utf-8') as f:
        hsp_list = json.load(f)
    return hsp_list

# 读取 clue868_library.xlsx 文件
def load_clue_library(library_path: str) -> pd.DataFrame:
    """Load the clue868 library from an Excel file."""
    df_library = pd.read_excel(library_path, dtype=str)
    # 仅保留 dose_daily_max不为空且 （unit == '最小制剂单位' 或者 mass 不为空）的行
    df_library = df_library[~df_library['dose_daily_max'].isna() & ((df_library['unit'] == '最小制剂单位') | (~df_library['mass'].isna()))]
    # 清理非法字符
    for col in df_library.columns:
        df_library[col] = df_library[col].apply(clean_illegal_chars)
    return df_library

# 获取 scene_item_ext 表中与库对应的数据
def fetch_sie(engine: Engine) -> pd.DataFrame:
    """Fetch scene_item_ext for a specific hospital abbreviation."""
    query = text("""
        SELECT DISTINCT item_std_code as item_code, item_name
        FROM scene_item_ext
        WHERE item_type IN ('西药费','中成药费')
    """)
    with engine.connect() as conn:
        df_scene = pd.read_sql(query, conn)
    # 清理非法字符
    for col in df_scene.columns:
        df_scene[col] = df_scene[col].apply(clean_illegal_chars)
    return df_scene

# 比对数据，判断是否需要扩充库
def compare_and_expand_library(df_library: pd.DataFrame, df_scene: pd.DataFrame):
    """Compare scene_item_ext data with the library and expand if necessary."""
    # 将两个df融合，如果 item_code == item_code 或者 item_name == item_name 则认为匹配，成为一个新的 df
    df_merged = pd.merge(df_scene, df_library, how='outer', on=['item_code', 'item_name'], indicator=True)
    df_matched = df_merged[df_merged['_merge'] == 'both']
    # 判断是否存在 item_code 不匹配但 item_name item_name
    df_name_only = df_merged[(df_merged['_merge'] == 'left_only') & (df_merged['item_name'].isin(df_library['item_name']))]

    handling_needed = not df_name_only.empty
    if handling_needed:
        print(f"发现 {len(df_name_only)} 条 item_name 匹配但 item_std_code 不匹配的记录。")
        user_input = input("是否需要扩充库？(y/n): ")
        if user_input.lower() == 'y':
            # 输出需要扩充的部分
            df_to_expand = df_name_only[['item_code', 'item_name']].drop_duplicates()
            expand_path = 'clue868_library_expand_temp.xlsx'
            df_to_expand.to_excel(expand_path, index=False)
            raise KeyboardInterrupt(f"请手动补全 unit 列后合并到 clue868_library.xlsx，临时文件已保存到 {expand_path}。")
        else:
            print("继续运行，不扩充库。")
    else:
        print("所有数据均匹配，无需扩充库。")
    
    # 将sie中真实存在的数据输出，即 item_code 可以匹配的部分，
    df_real_exist = df_scene[df_scene['item_code'].isin(df_library['item_code'])]
    # 按 item_code 继承 df_library 中的 mass, drug_class_ord, unit, dose_daily_max 列
    df_real_exist = pd.merge(df_real_exist, df_library[['item_code', 'mass', 'drug_class_ord', 'unit', 'dose_daily_max']], on='item_code', how='left')

    # 将 df_real 当中 的 item_code 去重作为列表，然后构造 in_clause
    item_code_exist_list = df_real_exist['item_code'].drop_duplicates().tolist()
    # in_clause 每1000个 item_code 分一组
    in_clauses = []
    chunk_size = 1000
    for i in range(0, len(item_code_exist_list), chunk_size):
        chunk = item_code_exist_list[i:i + chunk_size]
        in_clause = ",".join(f"'{code}'" for code in chunk)
        in_clauses.append(in_clause)
    # 从 sie 表查询对应 item_code 的 item_j_code
    df_sie_jcode = pd.DataFrame()
    with engine.connect() as conn:
        for in_clause in in_clauses:
            query = text(f"""
                SELECT DISTINCT item_std_code as item_code, item_j_code
                FROM scene_item_ext
                WHERE item_std_code IN ({in_clause})
            """)
            df_chunk = pd.read_sql(query, conn)
            df_sie_jcode = pd.concat([df_sie_jcode, df_chunk], ignore_index=True)
    # 将 df_real_exist 与 df_sie_jcode 合并，将 item_j_code 添加进去
    df_real_exist = pd.merge(df_real_exist, df_sie_jcode, on='item_code', how='inner')
    return df_real_exist

def upload_library(engine: Engine, df_real_exist: pd.DataFrame):
    """Upload the expanded library to the database (if needed)."""
    with engine.begin() as conn:
        # 判断是否有 clue868dict1 表存在，存在则删除
        conn.execute(text(f"""
            DECLARE
                v_count NUMBER;
            BEGIN
                SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'CLUE868DICT1';
                IF v_count > 0 THEN
                    EXECUTE IMMEDIATE 'DROP TABLE clue868dict1 PURGE';
                END IF;
            END;
        """))
        # 创建 clue868dict1 表
        conn.execute(text(f"""
            CREATE TABLE clue868dict1 (
                item_j_code NUMBER,
                item_code VARCHAR2(100),
                item_name VARCHAR2(500),
                mass NUMBER,
                drug_class_ord NUMBER,
                unit VARCHAR2(100),
                dose_daily_max NUMBER
            )
        """))
        # 将 df_library 去除 dose_daily_max 为空，或者 （unit != '最小制剂单位' 且 mass 为空）的行
        df_cleaned = df_real_exist[~df_real_exist['dose_daily_max'].isna() & ((df_real_exist['unit'] == '最小制剂单位') | (~df_real_exist['mass'].isna()))]
        # 上传数据
        df_cleaned.to_sql('clue868dict1', conn, if_exists='append', index=False)
        # 在 item_j_code, item_code 上分别
        conn.execute(text(f"""
            CREATE INDEX idx_clue868dict1_jcode ON clue868dict1 (item_j_code)
        """))
        conn.execute(text(f"""
            CREATE INDEX idx_clue868dict1_code ON clue868dict1 (item_code)
        """))

# 计算超量
def cal_exceed(engine: Engine, hsp_abbr: str):
    """Calculate exceed clues for a specific hospital abbreviation."""
    sql = text(f"""
        WITH t0 AS (
            -- 查出有多次使用对应药物的总剂量
            SELECT
                d.psn_no, 
                c.DRUG_CLASS_ORD, max(c.item_name) as item_name, max(c.unit) as unit, max(c.dose_daily_max) as dose_daily_max,
                min(d.item_day) as earliest_day, max(d.item_day) as latest_day,
                max(d.item_day) - min(d.item_day) as day_range_real,
                max(d.item_day) - min(d.item_day) + 90 as day_range_available,
                count(distinct d.setl_id) as case_count,
                count(distinct case when d.j_isin = '0' then d.setl_id else null end) as outpatient_count,
                count(distinct case when d.j_isin = '1' then d.setl_id else null end) as inpatient_count,
                sum(d.q) as sum_q,
                sum(CASE WHEN c.unit = '最小制剂单位' then d.q else d.q * c.mass END) as sum_mass,
                sum(d.c) as sum_c, sum(d.b) as sum_b
            FROM d, clue868dict1 c
            WHERE d.hsp_abbr = :hsp_abbr
                and d.item_j_code = c.item_j_code
            group by d.psn_no, c.drug_class_ord
            having max(d.item_day) - min(d.item_day) > 30
        )
        SELECT 
            mdict.psn_name as 姓名,	t0.psn_no as 个人编号,	mdict.certno as 身份证号,
            t0.item_name as 药品名称,	
            t0.case_count as 结算次数, t0.outpatient_count as 门诊结算次数, t0.inpatient_count as 住院结算次数,
            t0.earliest_day as 最早开单日期, t0.latest_day as 最晚开单日期,
            t0.sum_q as 总数量,
            t0.sum_c as 总金额, t0.sum_b as 总医保内金额,
            t0.unit as 剂量单位,	
            t0.sum_mass as 总药品剂量,
            t0.dose_daily_max as 每日最大剂量,
            t0.day_range_real	as 开单日间隔天数,
            t0.day_range_available as 处方允许天数,
            t0.sum_mass / t0.dose_daily_max as 实际用药天数,
            t0.sum_mass / t0.dose_daily_max - t0.day_range_available as 违规天数,
            ( t0.sum_mass / t0.dose_daily_max - t0.day_range_available ) / ( t0.sum_mass / t0.dose_daily_max ) as 违规天数占实际天数比,
            ( t0.sum_mass / t0.dose_daily_max - t0.day_range_available ) / ( t0.sum_mass / t0.dose_daily_max ) * t0.sum_q as 折算违规数量,
            ( t0.sum_mass / t0.dose_daily_max - t0.day_range_available ) / ( t0.sum_mass / t0.dose_daily_max ) * t0.sum_c as 折算违规金额,
            ( t0.sum_mass / t0.dose_daily_max - t0.day_range_available ) / ( t0.sum_mass / t0.dose_daily_max ) * t0.sum_b as 折算违规医保内金额
        FROM t0, (SELECT psn_no, MAX(psn_name) as psn_name, MAX(certno) as certno FROM m GROUP BY psn_no) mdict
        WHERE t0.sum_mass / t0.dose_daily_max > t0.day_range_available
            and t0.psn_no = mdict.psn_no
        ORDER BY 违规天数 desc
    """)
    with engine.connect() as conn:
        df_result = pd.read_sql(sql, conn, params={"hsp_abbr": hsp_abbr})
    if not df_result.empty:
        # df_result 删除非法字符
        for col in df_result.columns:
            df_result[col] = df_result[col].apply(clean_illegal_chars)
        # 判断各级输出目录是否存在，不存在则创建
        output_dir_pre = 'STEP8筛选/clue'
        os.makedirs(output_dir_pre, exist_ok=True)
        output_dir = fr'STEP8筛选/clue/{hsp_abbr}'
        os.makedirs(output_dir, exist_ok=True)
        # 输出结果到 excel
        output_path = fr'STEP8筛选/clue/{hsp_abbr}/clue868_bad_drug_exceed_{hsp_abbr}.xlsx'
        df_result.to_excel(output_path, index=False)
    return df_result


# 主程序
if __name__ == "__main__":

    # 读入
    df_library = load_clue_library('clue868_library.xlsx')
    df_scene = fetch_sie(engine)

    # 比对
    df_real_exist = compare_and_expand_library(df_library, df_scene)

    # 上传库到数据库
    upload_library(engine, df_real_exist)

    # 各医院运算超量
    hsp_list = load_hsp_list()

    for hsp_datum in hsp_list:
        hsp_abbr = hsp_datum['hsp_abbr']
        print(f"[{elapsed()}] 处理医院 {hsp_abbr} 的超量线索...")

        df_exceed = cal_exceed(engine, hsp_abbr)
        if df_exceed.empty:
            print(f"[{elapsed()}] 医院 {hsp_abbr} 无超量线索。")
        else:
            print(f"[{elapsed()}] 医院 {hsp_abbr} 超量线索已输出，共 {len(df_exceed)} 条记录。")